<?php

class Message extends Model {

    function add($message) {
        $subject = mysql_real_escape_string($message["subject"]);
        $author_user_id = mysql_real_escape_string($message["author_user_id"]);
        $content = mysql_real_escape_string($message["content"]);

        $query = "INSERT INTO `conversations` (
                    `subject`
                    )
                  VALUES (
                    '$subject'
                  );";

        $result = $this->query($query);
        $conversation_id = mysql_insert_id();

        foreach ($_SESSION['user_list'] as $user):
            $user = $this->get_user($user);
            $user_id = $user['User']['id'];
            $query = "INSERT INTO `participants` (
                        `conversation_id` ,
                        `user_id`
                        )
                      VALUES (
                        '$conversation_id',
                        '$user_id'
                      );";

            $result = $this->query($query);

            $query = "INSERT INTO `replies` (
                        `conversation_id` ,
                        `user_id`,
                        `isread`
                        )
                      VALUES (
                        '$conversation_id',
                        '$user_id',
                        0
                      );";

            $result = $this->query($query);
        endforeach;

        $query = "INSERT INTO `participants` (
                    `conversation_id` ,
                    `user_id`
                    )
                  VALUES (
                    '$conversation_id',
                    '$author_user_id'
                  );";

        $result = $this->query($query);

        $query = "INSERT INTO `replies` (
                    `conversation_id` ,
                    `user_id`,
                    `isread`
                    )
                  VALUES (
                    '$conversation_id',
                    '$author_user_id',
                    1
                  );";

        $result = $this->query($query);

        $query = "INSERT INTO `messages` (
                    `conversation_id` ,
                    `author_user_id` ,
                    `timestamp` ,
                    `content`
                    )
                  VALUES (
                    '$conversation_id',
                    '$author_user_id',
                    CURRENT_TIMESTAMP,
                    '$content'
                  );";

        $result = $this->query($query);
    }

    function add_replay($message) {
        $conversation_id = mysql_real_escape_string($message["conversation_id"]);
        $author_user_id = mysql_real_escape_string($message["author_user_id"]);
        $content = mysql_real_escape_string($message["content"]);

        $query = "UPDATE `replies`
                  SET
                    `isread` = 0
                  WHERE
                    `conversation_id` = $conversation_id";

        $result = $this->query($query);

        $query = "UPDATE `replies`
                  SET
                    `isread` = 1
                  WHERE
                    `conversation_id` = $conversation_id AND
                    `user_id` = $author_user_id";

        $result = $this->query($query);

        $query = "INSERT INTO `messages` (
                    `id` ,
                    `conversation_id` ,
                    `author_user_id` ,
                    `timestamp` ,
                    `content`
                    )
                  VALUES (
                    NULL,
                    '$conversation_id',
                    '$author_user_id',
                    CURRENT_TIMESTAMP,
                    '$content'
                  );";

        $result = $this->query($query);

        return $result;
    }

    function add_recipient() {
        $query = "SELECT * FROM `users`";

        $result = $this->query($query);
        return $result;
    }

    function view($user_id, $id) {
        $query = "UPDATE `replies`
                  SET
                    `isread` = 1
                  WHERE
                    `conversation_id` = $id AND
                    `user_id` = $user_id";

        $update = $this->query($query);

        $query = "SELECT
                    `users`.`id`,
                    `users`.`name`,
                    `photos`.`filename`,
                    `messages`.`timestamp`,
                    `messages`.`id`,
                    `messages`.`content`,
                    `messages`.`author_user_id`
                  FROM `messages` JOIN `users` JOIN `photos`
                  WHERE
                    `messages`.`conversation_id` = $id AND
                    `messages`.`author_user_id` = `users`.`id` AND
                    `photos`.`id` = `users`.`photo_id`
                  ORDER BY `messages`.`timestamp`";

        $message = $this->query($query);

        $query = "SELECT * FROM `conversations`
                  WHERE
                    `conversations`.`id` = $id";

        $conversation = $this->query($query, true);

        $result["Messages"] = $message;
        $result["Conversation"] = $conversation["Conversation"];
        return $result;
    }

    function view_ajax($id, $page) {
        $query = "SELECT
                    `users`.`id`,
                    `users`.`name`,
                    `photos`.`filename`,
                    `messages`.`timestamp`,
                    `messages`.`id`,
                    `messages`.`content`,
                    `messages`.`author_user_id`
                  FROM `messages` JOIN `users` JOIN `photos`
                  WHERE
                    `messages`.`conversation_id` = $id AND
                    `messages`.`author_user_id` = `users`.`id` AND
                    `photos`.`id` = `users`.`photo_id`
                  ORDER BY `messages`.`timestamp`";

        $temp = $this->query($query);
        $count = count($temp);
        $page_count = round($count / 3, 0, PHP_ROUND_HALF_UP);

        $offset = ($page_count - (int)$page) * 3;
        if ($offset < 0) {
            $offset = 1;
            $endoffset = 0;
        } else {
            if ($offset == 0 ) {
                $offset = 1;
                $endoffset = 2;
            } else {
                $endoffset = 3;
            }
        }

        if($page == 1) {
            $endoffset = 4;
        }

        $query = "SELECT
                    `users`.`id`,
                    `users`.`name`,
                    `photos`.`filename`,
                    `messages`.`timestamp`,
                    `messages`.`id`,
                    `messages`.`content`,
                    `messages`.`author_user_id`
                  FROM `messages` JOIN `users` JOIN `photos`
                  WHERE
                    `messages`.`conversation_id` = $id AND
                    `messages`.`author_user_id` = `users`.`id` AND
                    `photos`.`id` = `users`.`photo_id`
                  ORDER BY `messages`.`timestamp`
                  LIMIT $offset, $endoffset";

        $result["Messages"] = $this->query($query);

        $query = "SELECT * FROM `conversations`
                  WHERE
                    `conversations`.`id` = $id";

        $conversation = $this->query($query, true);
        $result["Conversation"] = $conversation["Conversation"];
        return $result;
    }

    function show($id) {
        $query = "SELECT
                    `messages`.`author_user_id`,
                    `messages`.`timestamp`,
                    `messages`.`content`,
                    `conversations`.`id`,
                    `conversations`.`subject`,
                    `photos`.`filename`,
                    `users`.`name`,
                    `users`.`id`,
                    `replies`.`isread`
                  FROM `participants` JOIN `conversations` JOIN `messages` JOIN `users` JOIN `photos` JOIN `replies`
                  WHERE
                    `users`.`photo_id` = `photos`.`id` AND
                    `users`.`id` = `messages`.`author_user_id` AND
                    `participants`.`user_id` = $id AND
                    `participants`.`conversation_id` = `conversations`.`id` AND
                    `conversations`.`id` = `messages`.`conversation_id` AND
                    `replies`.`conversation_id` = `messages`.`conversation_id` AND
                    `replies`.`user_id` = $id
                  GROUP BY `conversations`.`id`
                  ORDER BY `replies`.`isread`,`messages`.`timestamp` DESC";

        $result["Message"] = $this->query($query);

        $query = "SELECT
                    `participants`.*,
                    `users`.`name`
                  FROM `participants` JOIN `users`
                  WHERE `users`.`id` = `participants`.`user_id`";

        $result["Recipient"] = $this->query($query);

        return $result;
    }

    function show_ajax($id, $offset) {
        $query = "SELECT
                    `messages`.`author_user_id`,
                    `messages`.`timestamp`,
                    `messages`.`content`,
                    `conversations`.`id`,
                    `conversations`.`subject`,
                    `photos`.`filename`,
                    `users`.`name`,
                    `users`.`id`,
                    `replies`.`isread`
                  FROM `participants` JOIN `conversations` JOIN `messages` JOIN `users` JOIN `photos` JOIN `replies`
                  WHERE
                    `users`.`photo_id` = `photos`.`id` AND
                    `users`.`id` = `messages`.`author_user_id` AND
                    `participants`.`user_id` = $id AND
                    `participants`.`conversation_id` = `conversations`.`id` AND
                    `conversations`.`id` = `messages`.`conversation_id` AND
                    `replies`.`conversation_id` = `messages`.`conversation_id` AND
                    `replies`.`user_id` = $id
                  GROUP BY `conversations`.`id`
                  ORDER BY `replies`.`isread`,`messages`.`timestamp` DESC
                  LIMIT $offset, 3";

        $result["Message"] = $this->query($query);

        $query = "SELECT
                    `participants`.*,
                    `users`.`name`
                  FROM `participants` JOIN `users`
                  WHERE `users`.`id` = `participants`.`user_id`";

        $result["Recipient"] = $this->query($query);

        return $result;
    }

    function delete_replay($conversation_id, $message_id) {
        $query = "DELETE FROM `messages`
                  WHERE
                    `conversation_id` = $conversation_id AND
                    `id` = $message_id";

        $result = $this->query($query);
    }

    function delete($conversation_id, $user_id) {
        $query = "DELETE FROM `participants`
                  WHERE
                    `conversation_id` = $conversation_id AND
                    `user_id` = $user_id";

        $result = $this->query($query);

        $query = "DELETE FROM `replies`
                  WHERE
                    `conversation_id` = $conversation_id AND
                    `user_id` = $user_id";

        $result = $this->query($query);

        $query = "SELECT * FROM `participants`
                  WHERE
                    `conversation_id` = $conversation_id";

        $result = mysql_query($query);
        if (mysql_num_rows($result) == 1) {
            $query = "DELETE FROM `participants`
                      WHERE
                        `conversation_id` = $conversation_id";

            $result = $this->query($query);

            $query = "DELETE FROM `replies`
                      WHERE
                        `conversation_id` = $conversation_id";

            $result = $this->query($query);

            $query = "DELETE FROM `messages`
                      WHERE
                        `conversation_id` = $conversation_id";

            $result = $this->query($query);

            $query = "DELETE FROM `conversations`
                      WHERE
                        `id` = $conversation_id";

            $result = $this->query($query);
        }
    }

    function get_user_photos($user_id) {
        $query = "SELECT * FROM `photos` JOIN `users`
                  WHERE
                    `users`.`photo_id` = `photos`.`id` AND
                    `users`.`id` = '$user_id';";

        $result = $this->query($query, true);

        return $result;
    }

    function get_user($name) {
        $query = "SELECT * FROM `users`
                  WHERE
                    `users`.`name` = '$name';";

        $result = $this->query($query, true);

        return $result;
    }

}
